﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using NextAction.Domain.Entities;
using System.Data.SqlClient;
using System.Data;
using System.Data.Entity.Infrastructure;

namespace NextAction.Domain.Concrete
{
    public class EFDbContext : DbContext
    {
        public DbSet<GtdTask> GtdTasks { get; set; }

        public DbSet<GtdProject> GtdProjects { get; set; }

        public DbSet<Priority> Priorities { get; set; }

        public int GetTasksCount(
            int userID,
            GtdTaskType taskType,
            DateTime? minScheduleDate,
            DateTime? maxScheduleDate,
            bool? scheduleDateIsNotNull,
            bool? scheduleDateIsNull)
        {
            return Database.SqlQuery<int>(
                @"EXEC GetTasksCount 
                    @UserID, 
                    @TaskTypeID,
                    @MinScheduleDate,
                    @MaxScheduleDate,
                    @ScheduleDateIsNotNull,
                    @ScheduleDateIsNull",
                CreateIntParameter("@UserID", userID),
                CreateIntParameter("@TaskTypeID", (int)taskType),
                CreateDateParameter("@MinScheduleDate", minScheduleDate),
                CreateDateParameter("@MaxScheduleDate", maxScheduleDate),
                CreateBoolParameter("@ScheduleDateIsNotNull", scheduleDateIsNotNull),
                CreateBoolParameter("@ScheduleDateIsNull", scheduleDateIsNull)
                ).First();
        }

        public DbSqlQuery<GtdProject> GetProjects(
            int userID, 
            bool isCompleted, 
            bool orderByPriority,
            bool orderByName)
        {
            return GtdProjects.SqlQuery(
                @"EXEC GetProjects 
                    @UserID, 
                    @IsCompleted,
                    @OrderByPriority,
                    @OrderByName",
                CreateIntParameter("@UserID", userID),
                CreateBoolParameter("@IsCompleted", isCompleted),
                CreateBoolParameter("@OrderByPriority", orderByPriority),
                CreateBoolParameter("@OrderByName", orderByName));
        }

        public GtdProject GetProject(int projectID)
        {
            return GtdProjects.SqlQuery(
                @"EXEC GetProject 
                    @ProjectID",
                CreateIntParameter("@ProjectID", projectID)).First();
        }

        public DbSqlQuery<GtdTask> GetTasksForProject(int projectID)
        {
            return GtdTasks.SqlQuery(
                @"EXEC GetTasksForProject 
                    @ProjectID",
                CreateIntParameter("@ProjectID", projectID));
        }


        private SqlParameter CreateIntParameter(string name, int value)
        {
            return CreateParameter(name, SqlDbType.Int, value);
        }

        private SqlParameter CreateDateParameter(string name, DateTime? value)
        {
            return CreateParameter(name, SqlDbType.DateTime, value);
        }

        private SqlParameter CreateBoolParameter(string name, bool? value)
        {
            return CreateParameter(name, SqlDbType.Bit, value);
        }

        private SqlParameter CreateParameter(string name, SqlDbType type, object value)
        {
            return new SqlParameter(name, type)
            {
                Value = value ?? DBNull.Value
            };
        }

     }
}
